package com.mouse.mapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.mouse.entity.Disease;
import com.mouse.entity.GeneHomology;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author godson
 * @since 2022-08-07
 */
public interface GeneHomologyMapper extends BaseMapper<GeneHomology> {


    @Select("Select gho.* from gene_homology gho WHERE gho.gid=#{gid}")
    List<GeneHomology> getGeneHomologyByGid(@Param("gid") Long gid);


    @Select("SELECT * FROM (SELECT gh.* FROM (SELECT gc.* FROM gene_composition gcom LEFT JOIN genecategory_genecomposition gcgcom ON gcom.gcid=gcgcom.gcid LEFT JOIN gene_category gc ON gcgcom.gid=gc.gid WHERE gcom.gcid=#{gcid}) gc_result\n" +
            "LEFT JOIN gene_homology gh ON gc_result.gid = gh.gid) a where a.shid IS NOT NULL")
    List<GeneHomology> getGeneHomologyByGcid(@Param("gcid") Long gcid);
    /***
     * 3.2.2.3
     * 忽略了NULL值
     * @param did
     * @return
     */
    @Select("SELECT * FROM(" +
            "SELECT DISTINCT gh.* FROM\n" +
            "(SELECT gcda.* FROM  double_alleles da LEFT JOIN gene_composition_double_alleles gcda ON da.did=gcda.did WHERE da.did=1) res\n" +
            " LEFT JOIN gene_composition gcom on res.gcid=gcom.gcid \n" +
            "LEFT JOIN genecategory_genecomposition ggcom ON res.gcid=ggcom.gcid LEFT JOIN gene_category gc ON gc.gid=ggcom.gid\n" +
            "LEFT JOIN gene_homology gh ON gh.gid=gc.gid)"+
            " r WHERE r.sid IS NOT NULL")
    List<GeneHomology> getGeneHomologyByDid( @Param("did") Long did);



    @Select("SELECT a.* FROM (SELECT DISTINCT gh.* FROM gene_allele ga LEFT JOIN double_alleles_gene_allele daga ON ga.aid=daga.aid LEFT JOIN double_alleles da ON daga.did= da.did \n" +
            "LEFT JOIN gene_composition_double_alleles gcda ON gcda.did=da.did LEFT JOIN gene_composition gcom on gcda.gcid=gcom.gcid\n" +
            "LEFT JOIN genecategory_genecomposition gcgcom ON gcgcom.gcid=gcom.gcid LEFT JOIN gene_category gc ON gc.gid=gcgcom.gid\n" +
            "LEFT JOIN gene_homology gh ON gh.gid = gc.gid \n" +
            "WHERE daga.aid=#{aid}) a WHERE a.sid IS NOT NULL")
    List<GeneHomology> getGeneHomologyByAid(@Param("aid") Long aid);
}
